import sqlite3 as lite
import pdb

from odf.opendocument import OpenDocumentSpreadsheet
from odf.style import Style, TextProperties, ParagraphProperties, TableColumnProperties, TableCellProperties
from odf.text import P
from odf.table import Table, TableColumn, TableRow, TableCell

query = "select  evaluacion.idalumno,evaluacion.idactividad, evaluacion.idcriterio, evaluacion.evaluacion, criterios_actividad.toc, alumnos.nombre||' ' ||alumnos.apellidos as alumno, alumnos.clase,  actividades.actividad, criterios.criterio,evaluacion.idcriterio, evaluacion.evaluacion, criterios_actividad.toc from evaluacion join  criterios on evaluacion.idcriterio = criterios._id join criterios_actividad  on criterios_actividad.idcriterio = evaluacion.idcriterio and criterios_actividad.idactividad = evaluacion.idactividad join actividades on evaluacion.idactividad = actividades._id join alumnos on evaluacion.idalumno = alumnos._id  where alumnos.clase = '%s' order by nombre, criterio"  




clases = ['6A','6B','6C','5C']


def printData():
  textdoc = OpenDocumentSpreadsheet()
  tablecontents = Style(name="Table Contents", family="paragraph")
  tablecontents.addElement(ParagraphProperties(numberlines="false", linenumber="0"))
  tablecontents.addElement(TextProperties(fontweight="bold"))
  textdoc.styles.addElement(tablecontents)

  widthshort = Style(name="Wshort", family="table-column")
  widthshort.addElement(TableColumnProperties(columnwidth="2.7cm"))
  textdoc.automaticstyles.addElement(widthshort)

  widthwide = Style(name="Wwide", family="table-column")
  widthwide.addElement(TableColumnProperties(columnwidth="2.5in"))
  textdoc.automaticstyles.addElement(widthwide)

  greenBack= Style(name="green-background", family="table-cell")
  greenBack.addElement(TextProperties(fontweight="bold", fontsize="13", color="#ff00ff"))
  textdoc.automaticstyles.addElement(greenBack)
  
  redBack= Style(name="red-background", family="table-cell")
  redBack.addElement(TableCellProperties(backgroundcolor="#ff0000"))
  textdoc.automaticstyles.addElement(redBack)

  bld= Style(name="bold", family="table-cell")
  bld.addElement(TextProperties(fontweight="bold"))
  bld.addElement(TableCellProperties(backgroundcolor="#FFFFFF"))
  textdoc.automaticstyles.addElement(bld)
  table = Table(name=clase)
  table.addElement(TableColumn(numbercolumnsrepeated=4,stylename=widthshort))
  table.addElement(TableColumn(numbercolumnsrepeated=3,stylename=widthwide))
  for line in salida:
    tr = TableRow()
    table.addElement(tr)
    for element in line:
      cell = TableCell(stylename="bold")
      tr.addElement(cell)
      try:
        p = P(stylename=tablecontents,text=unicode(str(element),PWENC))
      except:
	pdb.set_trace()
      cell.addElement(p)
      
  textdoc.spreadsheet.addElement(table) 
  textdoc.save(clase+"_eval.ods")





for clase in clases:
  con = lite.connect('/tmp/urgentisimo.db')
  cur = con.cursor()    
  cur.execute("SELECT _id, nombre, apellidos from alumnos where clase = '"+clase+"' order by nombre")
  alumnosdata = cur.fetchall()
  #cur.execute('SELECT idalumno,idactividad,idcriterio,criterios_evaluacion.toc evaluacion from evaluacion join criterios_evaluacion on criterios_evaluacion.idactividad = evaluacion.idactividad, criterios_evaluacion.idcriterio = evaluacion.idcriterio order by 1,2,3')
  cur.execute(query % (clase))
  evaluacionesdata = cur.fetchall()
  cur.execute('select _id,actividad from actividades')
  actividadesdata = cur.fetchall()
  cur.execute('select _id,criterio from criterios')
  criteriosdata = cur.fetchall()
  evaluaciones = {}
  criterios = {}
  actividades = {}
  PWENC = "utf-8"

  for tmp in criteriosdata:
    try:
      criterios[tmp[0]] = tmp[1]
    except:
      pdb.set_trace()
  for tmp in actividadesdata:
    actividades[tmp[0]] = tmp[1]
    

  alumnos = {}
  for alumno in alumnosdata:
    alumnokey = alumno[1]+" "+alumno[2]
    alumnos[alumnokey] = {}
    for evaluacion in evaluacionesdata:
      if evaluacion[0] == alumno[0]:
	#pdb.set_trace()
	try:
	  alumnos[alumnokey][evaluacion[1]].append([evaluacion[2],evaluacion[3],evaluacion[4]])
	except:
	  alumnos[alumnokey][evaluacion[1]] = [[evaluacion[2],evaluacion[3],evaluacion[4]]]


  salida = []
  for al in alumnos.keys():
    salida.append([al.encode('utf8'),'',''])
    for act in alumnos[al].keys():
      nota = []
      fila = len(salida)
      salida.append(['',actividades[act].encode('utf8'),''])
      cr = alumnos[al][act]
      cnt = 0
      for c in cr:
	try:
	  toc = c[2]
	except:
	  None
	if c[2] == None or c[2] == 0:
	  if c[2] == 0 and c[1] == 0:
	    toc = 1
	  else:
	    toc = c[1]
	  c[2]=""
	try:
	  notaactividad = int(c[1])*100/int(toc)
          nota.append(notaactividad)
        except:
	  #pdb.set_trace()
	  None
	
	salida.append(['','',criterios[c[0]].encode('utf8'),str(notaactividad).encode('utf8')])
	cnt = cnt+1
	
      #pdb.set_trace()
      size = len(nota)
      if size == 0:
	size = 1
      salida[fila].append(sum(nota)/size)
      #pdb.set_trace()
      #try:
      #salida.append(['','',criterios[c[0]].encode('utf8')+","+c[1].encode('utf8')])
    #except:
      #pdb.set_trace()
    
  printData()   
    

	  
#for line in salida:
  #b = str(line).strip('[]')+'\n'
  #file.write(b)
      
      
    
